﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TalentPools.Model;
namespace TalentPools.DAL
{
    public class CreateresumeService
    {
        #region 添加个人的简历信息
        /// <summary>
        /// 添加简历信息，默认返回false
        /// </summary>
        /// <param name="re"></param>
        /// <returns></returns>
        public bool insertresumeInfo(resume re)
        {
            string sql = "insert into resume(username,name,sex,birthday,marriage,card,politicsStatus,HKPlace,city,currentAddress,zipCode,cellphone,phoneCode,email,homePage,workYear,isPublic,createTime,lastUpdateTime,resumeName,isTj,isPb)values(@userId,@name,@sex,@birthday,@marriage,@card,@politicsStatus,@HKPlace,@city,@currentAddress,@zipCode,@cellphone,@phoneCode,@email,@homePage,@workYear,@isPublic,@createTime,@lastUpdateTime,@resumeName,@isTj,@isPb)";
            SqlParameter[] par = {
					new SqlParameter("@userId",re.username),
					new SqlParameter("@name",re.name),
					new SqlParameter("@sex", re.sex),
					new SqlParameter("@birthday", re.birthday),
					new SqlParameter("@marriage", re.marriage),
					new SqlParameter("@Card", re.card),
					new SqlParameter("@politicsStatus", re.politicsStatus),
					new SqlParameter("@HKPlace",  re.HKPlace),
					new SqlParameter("@city",  re.city),
					new SqlParameter("@currentAddress", re.currentAddress),
					new SqlParameter("@zipCode", re.zipCode),
					new SqlParameter("@cellphone", re.cellphone),
                    new SqlParameter("@phoneCode",re.phoneCode),
					new SqlParameter("@email", re.email),
					new SqlParameter("@homePage",re.homePage),
                    new SqlParameter("@workYear",re.workYear),
                    new SqlParameter("@isPublic",re.isPublic),
                    new SqlParameter("@lastUpdateTime",re.lastUpdateTime),
                    new SqlParameter("@createTime",re.createTime),
                    new SqlParameter("@resumeName",re.resumeName),
                    new SqlParameter("@isTj",false),
                    new SqlParameter("@isPb",false)
                               };
            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, par);
            if (i > 0)
            {
                return true;
            }
            return false;
        }
        #endregion
        #region 查找最新的简历表的ID
        /// <summary>
        /// 查找最新的简历表的ID
        /// </summary>
        /// <returns></returns>
        public int getresumeId(string id)
        {
            string sql = "select top 1 id from resume where username=@id order by createTime desc";
            SqlParameter[] par = { new SqlParameter("@id", id) };
            return (int)DBHelper.ExecuteNonQuery(CommandType.Text, sql, par);
        }
        /// <summary>
        ///通过登录名和简历创建时间获取简历表的编号
        /// </summary>
        /// <param name="userId">用户编号</param>
        /// <param name="createTime">简历创建时间</param>
        /// <returns></returns>
        public int GetResumeId(string userId, DateTime createTime)
        {
            string sql = "select Id from resume where username=@userId and createTime=@createTime";
            SqlParameter[] paras = new SqlParameter[] { 
          new SqlParameter("@userId",userId),
          new SqlParameter("@createTime",createTime)
          };
            return Convert.ToInt32(DBHelper.ExecuteScalar(CommandType.Text, sql, paras));
        }
        #endregion
        #region 创建个人求职意向表
        /// <summary>
        /// 创建个人求职意向表
        /// </summary>
        /// <param name="inte">求职意向表对象</param>
        /// <returns></returns>
        public bool createIntention(intention inte)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into intention(");
            strSql.Append("resumeId,jobCategory,area,profession,industry,pay,state)");
            strSql.Append(" values (");
            strSql.Append("@resumeId,@jobCategory,@area,@profession,@industry,@pay,@state)");
            SqlParameter[] par ={
					new SqlParameter("@resumeId", inte.resumeId),
					new SqlParameter("@jobCategory", inte.jobCategory),
					new SqlParameter("@area", inte.area),
					new SqlParameter("@profession", inte.profession),
					new SqlParameter("@industry", inte.industry),
					new SqlParameter("@pay", inte.pay),
					new SqlParameter("@state",inte.state)};
            int i = DBHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), par);
            if (i > 0)
            {
                return true;
            }
            return false;
        }
        #endregion
        #region 创建个人培训信息表
        /// <summary>
        /// 创建个人培训信息表
        /// </summary>
        /// <param name="tr"></param>
        /// <returns></returns>
        public bool createTraning(training tr)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into training(");
            strSql.Append("resumeId,tittle,beginTime,endTime,result,details)");
            strSql.Append(" values (");
            strSql.Append("@resumeId,@tittle,@beginTime,@endTime,@result,@details)");
            SqlParameter[] par ={
					new SqlParameter("@resumeId",tr.resumeId),
					new SqlParameter("@tittle", tr.tittle),
					new SqlParameter("@beginTime", tr.beginTime),
					new SqlParameter("@endTime", tr.endTime),
					new SqlParameter("@result", tr.result),
					new SqlParameter("@details", tr.details)};
            int i = DBHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), par);
            if (i > 0)
            {
                return true;
            }
            return false;
        }
        /// <summary>
        /// 获取培训信息
        /// </summary>
        /// <param name="resumeId"></param>
        /// <returns></returns>
        public List<training> GetTraining(int resumeId)
        {
            string sql = "select trainingId,title,beginTime,endTime,result,details from training where resumeId=@resumeId";
            SqlParameter para = new SqlParameter("@resumeId", resumeId);
            List<training> list = new List<training>();
            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, sql, para))
            {
                while (dr.Read())
                {
                    training train = new training();
                    train.trainingId = Convert.ToInt32(dr["trainingId"]);
                    train.tittle = dr["title"].ToString();
                    train.beginTime = Convert.ToDateTime(dr["beginTime"]);
                    train.endTime = Convert.ToDateTime(dr["endTime"]);
                    train.result = dr["result"].ToString();
                    train.details = dr["details"].ToString();
                    list.Add(train);
                }
                return list;
            }
        }
        #endregion
        #region 创建个人工作表
        /// <summary>
        /// 创建个人工作表
        /// </summary>
        /// <param name="re"></param>
        /// <returns></returns>
        public bool creatRecord(record re)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into record(");
            strSql.Append("resumeId,title,beginTime,endTime,content,details)");
            strSql.Append(" values (");
            strSql.Append("@resumeId,@title,@beginTime,@endTime,@content,@details)");
            SqlParameter[] parameters = {
                   new SqlParameter("@resumeId", re.resumeId),
					new SqlParameter("@title", re.title),
					new SqlParameter("@beginTime", re.beginTime),
					new SqlParameter("@endTime", re.endTime),
					new SqlParameter("@content", re.content),
					new SqlParameter("@details",re.details)};
            int i = DBHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
            if (i > 0)
            {
                return true;
            }
            return false;
        }
        /// <summary>
        /// 获取工作履历
        /// </summary>
        /// <param name="resumeId"></param>
        /// <returns></returns>
        public List<record> GetRecord(int resumeId)
        {
            string sql = "select recordid,title,beginTime,endTime,content,details from record where resumeId=@resumeId";
            SqlParameter para = new SqlParameter("@resumeId", resumeId);
            List<record> list = new List<record>();
            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, sql, para))
            {
                while (dr.Read())
                {
                    record record = new record();
                    record.recordid = Convert.ToInt32(dr["recordid"]);
                    record.title = dr["title"].ToString();
                    record.beginTime = Convert.ToDateTime(dr["beginTime"]);
                    record.endTime = Convert.ToDateTime(dr["endTime"]);
                    record.content = dr["content"].ToString();
                    record.details = dr["details"].ToString();
                    list.Add(record);
                }
                return list;

            }
        }
        #endregion
        #region 创建自定义的表
        /// <summary>
        /// 创建自定义的表
        /// </summary>
        /// <param name="cus"></param>
        /// <returns></returns>
        public bool createCustom(custom cus)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into custom(");
            strSql.Append("resumeId,tittle,content)");
            strSql.Append(" values (");
            strSql.Append("@resumeId,@tittle,@content)");
            SqlParameter[] parameters ={
					new SqlParameter("@resumeId", cus.resumeId),
					new SqlParameter("@tittle", cus.tittle),
					new SqlParameter("@content", cus.content)};
            int i = DBHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
            if (i > 0)
            {
                return true;
            }
            return false;
        }
        /// <summary>
        /// 获取自定义信息
        /// </summary>
        /// <param name="resumeId"></param>
        /// <returns></returns>
        public List<custom> GetCustom(int resumeId)
        {
            string sql = "select customId,tittle,content from custom where resumeId=@resumeId";
            SqlParameter para = new SqlParameter("@resumeId", resumeId);
            List<custom> list = new List<custom>();
            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, sql, para))
            {
                while (dr.Read())
                {
                    custom custom = new custom();
                    custom.customId = Convert.ToInt32(dr["customId"]);
                    custom.tittle = dr["tittle"].ToString();
                    custom.content = dr["content"].ToString();
                    list.Add(custom);
                }
                return list;
            }
        }
        #endregion

        #region 设置简历的公开状态
        public string SetResumeState(string username, int resumeId)
        {
            try
            {
                string sql = "update resume set isPublic=0 where username=@username and Id<>@resumeId";
                SqlParameter[] paras = new SqlParameter[] { 
                    new SqlParameter("@username",username),
                    new SqlParameter("@resumeId",resumeId)
                };
                DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras);
                return "";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
        #endregion
    }
}
